
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spShippingStatusReportGetMissingDataReport]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [spShippingStatusReportGetMissingDataReport]
GO


CREATE PROCEDURE spShippingStatusReportGetMissingDataReport	
@OfficeID int,
@Type int
AS
SELECT ID, REQ, 
		CASE 
			WHEN OrderDate IS NOT NULL AND OrderDate > '1/1/1900' THEN OrderDate
		END OrderDate,			
		ContainerSize, LP, PONo_Office, PONo_Type, PONo_Supplier, PONo_Number, IsPrimary, 
		CASE 
			WHEN PONo_Consolidation <> 0 THEN PONo_Consolidation
		END PONo_Consolidation,		 
		AccNo, CustomerOrLocation, FinalDestination, [Description], 
		CASE 
			WHEN FactoryETD IS NOT NULL AND FactoryETD > '1/1/1900' THEN FactoryETD
		END FactoryETD,			

		CASE 
			WHEN ShippingDate IS NOT NULL AND ShippingDate > '1/1/1900' THEN ShippingDate
		END ShippingDate,			

		ShippingLine, Vessel, ContainerNo, 

		CASE 
			WHEN ETA IS NOT NULL AND ETA > '1/1/1900' THEN ETA
		END ETA,			

		[Week], ArrivalPort, PI, Payment, DocReceived, 
		CASE 
			WHEN CompletedDate IS NOT NULL AND CompletedDate > '1/1/1900' THEN CompletedDate
		END CompletedDate,
		BulkType, 
		CASE 
			WHEN BulkOrderNo <> 0 THEN BulkOrderNo
		END BulkOrderNo,
		[PayAuth],
		CASE 
			WHEN ContainerDeliveried IS NOT NULL AND ContainerDeliveried > '1/1/1900' THEN ContainerDeliveried
		END ContainerDeliveried,
		ModifiedDate,
		getDate() CurrentDate,
		CASE 
			WHEN POInOpera IS NOT NULL AND POInOpera > '1/1/1900' THEN POInOpera
		END POInOpera,	
		CASE 
			WHEN InvValue <> 0 THEN InvValue
		END InvValue,		
		CASE 
			WHEN PayDate IS NOT NULL AND PayDate > '1/1/1900' THEN PayDate
		END PayDate,		
		Remarks,
		SuppOrderNo,
		CASE 
			WHEN SInvoiced <> 0 THEN SInvoiced
		END SInvoiced,
		OnHold,
		[OfficeID],
		[CustPONo],
		[BulkOrderOfficeID],
		[SInvoicedPd],
	[Catalogue]
FROM
	[ShippingStatusReport] 
WHERE 
	(OfficeID = @OfficeID)  
	AND ((@Type = 0 AND CompletedDate IS NULL) OR @Type=1)
	AND
	((OrderDate is null OR OrderDate = '1/1/1900')
	OR ([LP] is null OR [LP] = '')
	OR ([ContainerSize] is null or [ContainerSize]='')
	OR (PONo_Type is null OR PONo_Type='')
	OR ([PONo_Supplier] is null OR [PONo_Supplier]='')
	OR ([PONo_Number] is null OR [PONo_Number]='')
	OR ([IsPrimary] is null OR [IsPrimary]='')
	OR ([PONo_Consolidation] is null OR [PONo_Consolidation]=0)
	OR (AccNo is null OR AccNo='')
	OR (CustomerOrLocation is null OR CustomerOrLocation='')
	OR (FinalDestination is null OR FinalDestination='')
	OR (Description is null OR Description='')
	OR (ArrivalPort is null OR ArrivalPort=''))
	
	
ORDER BY [REQ] asc


GO

	
